import pdfplumber
# import re
import regex as re

from datetime import datetime


import traceback # 打印更加详细的错误信息


# pip install xlwings
import xlwings as xw

import os # 获取路径用
import shutil # 移动文件


# 或者, 不知道打开了谁, 但是可以定位到正在激活的excel
wb:xw.Book = xw.books.active
print(wb.name)

# 获取活动工作表
# sheet = wb.sheets.active
sheet = wb.sheets['B_R']




def extract_text_from_pdf(pdf_path):
    # 打开PDF文件
    with pdfplumber.open(pdf_path) as pdf:
        # 初始化一个空字符串来存储所有的页面文本
        text = ""

        # 遍历PDF文件中的每一个页面
        for page in pdf.pages:
            # 提取当前页面的文本
            page_text = page.extract_text()
            
            # 将当前页面的文本添加到总文本中，并加上适当的换行以便区分不同页面
            text += page_text + "\n"  # 添加换行符可以根据需要调整

    return text




# pattern = r"\n *[0-9]{1,2} +/[24][05]' +.*"
# regex = re.compile(pattern, re.IGNORECASE | re.DOTALL)
# matches = re.findall(pattern, pdf_text, re.IGNORECASE)
# print(matches)

def reFromPattern(pattern, fromText:str) -> list:
    return re.findall(pattern, fromText, re.IGNORECASE)


def get_desktop_path():
    return os.path.expanduser("~/Desktop")


deskPath = get_desktop_path()
filesFolder = f"{deskPath}\\~bb"
pdfFilesPaths = [os.path.join(filesFolder, filename) for filename in os.listdir(filesFolder) if (filename.endswith('.PDF') or filename.endswith('.pdf')) ]

newDatas = []
def getDatasFromPDFPath(datas):


    for p in pdfFilesPaths:
        
        print('\n\n*********************************************************************************')
        print(p)

        # 使用上述函数提取PDF文本
        # pdf_text = extract_text_from_pdf("C:\\Users\\flx\\Desktop\\SB1T2SPY.PDF")
        pdf_text = extract_text_from_pdf(p)
        # print(pdf_text)

        # 提单号
        # BOOKING NUMBER(订舱号码): 155400012302 APPLICATION NO.:24012607209980   
        # (?<=\n *booking number[^:]*: *)[0-9]{12}
        # bl = reFromPattern(r'(?<=\n *booking number[^:]*: *|\n *booking confirmation[^:]*: *)[0-9]{12}', pdf_text)[0]
        bl = reFromPattern(r'[0-9]{12}', pdf_text)[0]
        # print(bl)
        # input("waiting...")


        # 版本号
        # ******** 配载通知 / REVISE : 000 DATE: 2024/02/27 15:44:04 PM *********
            # 中的 000
        # (?<=revise *: *)[0-9]{3}
        version = reFromPattern(r'(?<=revise *: *)\S+', pdf_text)[0]
        # print(version)


        # 确认时间
        # ******** 配载通知 / REVISE : 000 DATE: 2024/02/27 15:44:04 PM *********
            # 中的 2024/02/27 15:44:04
        # (?<=revise *:.*date *: *)[0-9]{2,4}[/\.][0-9]{2}[/\.][0-9]{2} +[0-9]{2}[:\.][0-9]{2}[:\.][0-9]{2}
        bkConfirmDatetime = reFromPattern(r'(?<=revise *:.*date *: *)[0-9]{2,4}[/\.][0-9]{2}[/\.][0-9]{2} +[0-9]{2}[:\.][0-9]{2}[:\.][0-9]{2}', pdf_text)[0]
        bkConfirmDatetime = str(bkConfirmDatetime).replace('-','/')
        bkConfirmDatetime = datetime.strptime(bkConfirmDatetime, '%Y/%m/%d %H:%M:%S')
        # print(bkConfirmDatetime)
        # print(type(bkConfirmDatetime))



        # 放柜公司
        # 空箱提领处 :湛江港国际集装箱码头有限公司
        # (?<=(提取處|提领处) *: *).*
        releaseParty = reFromPattern(r'(?<=[提取處|提领处] *: *).*', pdf_text)[0]
        # print(releaseParty)



        # 开仓时间
        # 一年多以来都不用这个字段了, 留空
        openDatetime = ""


        # 柜型
        # 柜量
        # 毛重
        # 空箱提领处
        # 2 /40' HI-CUBE 16,000.00 +8,400 26,280 (NON-HAZARDOUS)
        # 可能有多个match, 可以在里面做小括号, 形成group来进一步获取
        # \n[0-9]{1,2} +/[24][05]' +.*
        ctnrs = []
        cs = reFromPattern(r'[0-9]{1,2} +/[24][05]\'[^霞]+', pdf_text)
        ctnrTypeDic = {'REEFER HI-CUBE':'RH', 'HI-CUBE':'HQ', 'STANDARD DRY':'GP', 'REEFER':'RF', 'TANK':'TK'}
        # print(cs)
        for m in cs:
            
            ctnrCount = reFromPattern(r'[0-9]{1,2}(?= +/)', m)[0]
            ctnr20or40 = reFromPattern(r'(?<=[0-9]{1,2} /)[0-9]{2}', m)[0]
            ctnrType = reFromPattern(r'(?<=\' +)[^0-9]*', m)[0].strip()
            ctnrType = ctnr20or40 + ctnrTypeDic[ctnrType]
            ctnrStr = ctnrCount + 'X' + ctnrType

            ctnrPickUpPlace = reFromPattern(r'(?<=提.*: *)\S.*', m)[0]
            specialHandling = reFromPattern(r'(?<=\n *special hand[^:]*: *).*', m)[0] if reFromPattern(r'(?<=\n *special hand[^:]*: *).*', m) else "" 
            
            ctnrTemp = reFromPattern(r'(?<=temperature[^:]*: *)\S.*', m)[0] if reFromPattern(r'(?<=temperature[^:]*: *)\S.*', m) else ""
            
            ctnrGWTuple0 = reFromPattern(r'([0-9]{0,4},?[0-9\.]*) {0,4}\+([0-9]{1,3},[0-9]{2,3})', m)[0]
            ctnrNW, ctnrTareW = ctnrGWTuple0
            ctnrNW = int(float(ctnrNW.replace(',','')))
            ctnrTareW = int(float(ctnrTareW.replace(',','')))
            ctnrGW = ctnrNW + ctnrTareW
            ctnrLimit = int(reFromPattern(r'(?<=[0-9]{0,4},?[0-9\.]* {0,4}\+[0-9]{1,3},[0-9]{2,3} +)[0-9]{1,3},[0-9]{2,3}', m)[0].replace(',','')) if reFromPattern(r'(?<=[0-9]{0,4},?[0-9\.]* {0,4}\+[0-9]{1,3},[0-9]{2,3} +)[0-9]{1,3},[0-9]{2,3}', m) else 0

            ctnrs.append((ctnrCount, ctnrType, ctnrStr, ctnrNW, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW))
            # ctnrs.append({'ctnrCount':ctnrCount, 'ctnrType':ctnrType, 'ctnrStr':ctnrStr, 'ctnrNW':ctnrNW, 'ctnrPickUpPlace':ctnrPickUpPlace, 'specialHandling':specialHandling, 'ctnrTemp':ctnrTemp, 'ctnrLimit':ctnrLimit, 'ctnrGW':ctnrGW})
            
        # print(ctnrs)

        # 250821 由于长荣大量so变为网上订舱, 多个柜会变成多个1个柜的情况, 所以特别这样处理
        ctnrsDic = {}
        for c in ctnrs:
            ctnrCount, ctnrType, ctnrStr, ctnrNW, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW = c
            itsKey = ctnrType + ctnrPickUpPlace + specialHandling + ctnrTemp
            if not itsKey in ctnrsDic:
                ctnrsDic[itsKey] = (ctnrCount, ctnrType, ctnrStr, ctnrNW, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW)
            else:
                ctnrCount_old, _, _, ctnrNW_old, _, _, _, _, ctnrGW_old = ctnrsDic[itsKey]
                ctnrCount_new = str(int(ctnrCount_old) + int(ctnrCount))
                ctnrNW_new = str(float(ctnrNW_old) + float(ctnrNW))
                ctnrGW_new = str(float(ctnrGW_old) + float(ctnrGW))
                ctnrsDic[itsKey] = (ctnrCount_new, ctnrType, ctnrStr, ctnrNW_new, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW_new)


        # 卸货港
        # (?<=port of discharg[^:]+: *).*
        pod = reFromPattern(r'(?<=port of discharg[^:]+: *)\S.*', pdf_text)[0]
        # print(pod)


        # 温度
        # TEMPERATURE(温度):-20.0C / PRE-COOL:NO / GEN SET:1 / VENT SET:0(CMH)
        # (?<=\n *temp[^:]+: *).*


        # 特殊要求, 比如超重, 比如dry什么的, 但这个可能需要跟柜型一起弄... , 而且也有多个...可能要取一个有内容的覆盖全部...
        # SPECIAL HANDLING :HEAVY WEIGHT
        # (?<=\n *special hand[^:]*: *).*


        # 如果是柜型, 温度, 特殊需求 一起处理, 应该这么搞, 然后用group, 或者二次处理...
        # \n[0-9]{1,2} +/[24][05]' +[^提]*



        # 货名
        # COMMODITY(货物品名) :PAPER
        # (?<=commodity.*: *).*
        cargo = reFromPattern(r'(?<=commodity.*: *).*', pdf_text)[0]
        # print(cargo)


        # 运输方式
        # 这个字段好久不用管了
        trasMode = ''



        # 给谁
        # TO:CARUN INTERNATIONAL LOGISTICS CO.,LTD.
        # (?<=\nto:).*
        forwarder = reFromPattern(r'(?<=\nto:).*', pdf_text)[0] if reFromPattern(r'(?<=\nto:).*', pdf_text) else ''
        if forwarder == '':
            forwarder = reFromPattern(r'(?<=customer.*:\s*)\S.*',pdf_text)[0] if reFromPattern(r'(?<=customer.*:\s*)\S.*',pdf_text) else ''
        # print(forwarder)


        # 货主 , 有的时候没有这个字段, 就留空
        # SHIPPER (发货人) :PATENT INTERNATIONAL LOGISTICS(SHENZHEN) CO.LTD. ZHANJIANG BRANCH
        # (?<=\n *shipper.*: *).*
        shipper = reFromPattern(r'(?<=\n *shipper.*: *).*', pdf_text)[0] if reFromPattern(r'(?<=\n *shipper.*: *).*', pdf_text) else ""
        # print(shipper)



        # 毛重
        # 1 /40' HI-CUBE 27,000.00 +4,200 26,280 (NON-HAZARDOUS)
        # (?<=\n[0-9]{1,2} +/[24][05]' +[^0-9]+)[0-9].*\+[0-9][^ ]*
        # 拿到 27,000.00 +4,200, 再处理吧...



        # 船名航次
        # (?<=\nvessel/voyage.*: *)\S+.*
        # VESSEL/VOYAGE(船名/航次): KYOTO TOWER 0022-083B
        velvoy = reFromPattern(r'(?<=\n\s*vessel/voyage.*: *)\S+.*', pdf_text)[0] if reFromPattern(r'(?<=\n\s*vessel/voyage.*: *)\S+.*', pdf_text) else ""
        # print("velvoy1:" + velvoy)

        if velvoy.strip() == "": # 针对010开头的, 船名航次不在voy:这样一行里, 只能单独处理
            velvoy = reFromPattern(r'[A-Z ]+[0-9]{0,3} [0-9]{4}-[0-9]{3}[A-Z]', pdf_text)[0] if reFromPattern(r'[A-Z ]+[0-9]{0,3} [0-9]{4}-[0-9]{3}[A-Z]', pdf_text) else ""
            # print("velvoy2:" + velvoy)


        vel = ""
        voy = ""
        if velvoy != "":
            # print(velvoy)
            vel = reFromPattern(r'.*(?= [0-9]{4}-[0-9]{3}[a-z])', velvoy)[0]
            voy = reFromPattern(r'[0-9]{4}-[0-9]{3}[a-z]', velvoy)[0].replace('-','')
            # print(vel)
            # print(voy)
        else:
            velvoy = reFromPattern(r'\b[a-z]|-.+[0-9]{4}-[0-9]{3}[a-z]\b', pdf_text)[0]
            vel = reFromPattern(r'.*(?= [0-9]{4}-[0-9]{3}[a-z])', velvoy)[0]
            voy = reFromPattern(r'[0-9]{4}-[0-9]{3}[a-z]', velvoy)[0].replace('-','')
            
        # velvoy = velvoy.replace('-','')
        velvoy = vel + ' ' + voy

        # 装货港
        # (?<=\n *port of loading.*: *)\S[a-z -,]*
        # PORT OF LOADING (装货港):ZHANJIANG, GUANGDONG, 请scan QR code，或参考下列网址:
        pol = reFromPattern(r'(?<=\n *port of loading.*: *)\S[a-z -]*(?=,)', pdf_text)[0].strip()
        # print(pol)


        # 截关时间
        # (?<=\n截关时间 *: *|cut off[^:]*:)\S.*
        # VGM CUT OFF via EDI/WEB/APP :2024/03/01 12:00
        cutOffTime = reFromPattern(r'(?<=\n截关时间 *: *|cut off[^:]*:)\S[^a-z\n]*', pdf_text)[0].strip().replace('-','/') if reFromPattern(r'(?<=\n截关时间 *: *|cut off[^:]*:)\S[^a-z\n]*', pdf_text) else ""
        cutOffTime = datetime.strptime(cutOffTime, '%Y/%m/%d %H:%M') if cutOffTime != "" else datetime(2045, 1, 1)
        # print(cutOffTime)
        # print(type(cutOffTime))



        # 重柜交还处
        # (?<=\n.*交還處 *: *|\n.*返还处 *: *)\S.*
        # 重箱返还处 :湛江港国际集装箱码头有限公司
        returnPlace = reFromPattern(r'(?<=\n.*交還處 *: *|\n.*返还处 *: *)\S.*', pdf_text)[0]
        # print(returnPlace)



        # 合计集装箱
        # 用代码直接写了, 跟正则无关
        # ctnrStr = ''


        # 此柜总毛重
        # 这个可能有多个, 也需要从matches数组中多次获取
        # 来自这个表达式, 但还需二次处理
        # 1 /40' HI-CUBE 27,000.00 +4,200 26,280 (NON-HAZARDOUS)
        # (?<=\n[0-9]{1,2} +/[24][05]' +[^0-9]+)[0-9].*\+[0-9][^ ]*
        # 拿到 27,000.00 +4,200, 再处理吧...


        # 船名
        # 对前面 船名航次 进行二次处理
        # 也可以用这个的group来处理 (?<=vessel/voyage.*: *)(\S[a-z 0-9]+) ([0-9]{4}-[0-9]{3}[a-z])
        # VESSEL/VOYAGE(船名/航次): KYOTO TOWER 0022-083B



        # 航次
        # 对前面 船名航次 进行二次处理
        # 也可以用这个的group来处理 (?<=vessel/voyage.*: *)(\S[a-z 0-9]+) ([0-9]{4}-[0-9]{3}[a-z])
        # VESSEL/VOYAGE(船名/航次): KYOTO TOWER 0022-083B



        # 中转港
        # T/S PORT :HONG KONG (ZHU JIANG)
        # (?<=\n *t/s port *: *)\S.*
        # 但是还需要对 HONG KONG (ZHU JIANG)等进行二次转换
        transPort = reFromPattern(r'(?<=\n *t/s port *: *)\S.*' , pdf_text)[0] if reFromPattern(r'(?<=\n *t/s port *: *)\S.*' , pdf_text) else ''
        if 'zhu jiang' in  transPort.replace('  ',' ').replace('  ',' ').lower():
            transPort = 'HKHIT'
        elif 'pearl river' in transPort.replace('  ',' ').replace('  ',' ').lower():
            transPort = 'HKHK2'
        elif 'hong kong' in transPort.replace('  ',' ').replace('  ',' ').lower():
            transPort = 'HKHKG'

        # print(transPort)


        # 限重 -> 其实应该是总重, 最初做长荣的限重已经没有意义了
        # 1 /40' HI-CUBE 27,000.00 +4,200 26,280 (NON-HAZARDOUS)
        # (?<=\n[0-9]{1,2} +/[24][05]' +[^0-9]+)[0-9].*\+[0-9][^ ]*
        # 拿到 27,000.00 +4,200, 再处理吧...


        # 交货地
        # PLACE OF DELIVERY (交货地):KARACHI,PAKISTAN
        # (?<=\n *place of delivery.*: *|\n *final destination.*: *)\S.*
        finalDestination = reFromPattern(r'(?<=\n *place of delivery.*: *|\n *final destination.*: *)\S.*', pdf_text)[0]
        # print(finalDestination)


        # datas = []

        isCanAdd = True
        # 判断当前已经生成的newDatas中, 有没有存在bl一样的数据
        for nd in datas:
            if nd[0] == bl:
                if nd[2] <= bkConfirmDatetime: # booking确认时间, 小于即将进入的booking的确认时间
                    nd[0] = 'O_' + bl
                else: # 此时新的数据中, 已经有版本号大于即将放入的数据的内容
                    isCanAdd = False

        # 这个时候可以加入了
        if isCanAdd:
            # for cn in ctnrs:
            #     ctnrCount, ctnrType, ctnrStr, ctnrNW, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW = cn
            #     datas.append([bl, version, bkConfirmDatetime, releaseParty, '', ctnrType, ctnrCount, pod, ctnrTemp, specialHandling, cargo, trasMode, forwarder, shipper, ctnrNW, velvoy, pol, cutOffTime, returnPlace, ctnrStr, ctnrGW, vel, voy, transPort, ctnrLimit, finalDestination])
            for val in ctnrsDic.values():
                ctnrCount, ctnrType, ctnrStr, ctnrNW, ctnrPickUpPlace, specialHandling, ctnrTemp, ctnrLimit, ctnrGW = val
                datas.append([bl, version, bkConfirmDatetime, releaseParty, '', ctnrType, ctnrCount, pod, ctnrTemp, specialHandling, cargo, trasMode, forwarder, shipper, ctnrNW, velvoy, pol, cutOffTime, returnPlace, ctnrStr, ctnrGW, vel, voy, transPort, ctnrLimit, finalDestination])


        # print(datas)



# getDatasFromPDFPath(newDatas)
# print(newDatas)


try:
    # 可能会引发异常的代码
    getDatasFromPDFPath(newDatas)
# except SomeSpecificException as e:
#     # 处理特定异常的代码
#     print(f"Caught an exception: {e}")
# except AnotherSpecificException:
#     # 处理另一个特定异常的代码
#     print("Caught another specific exception.")
except Exception as e:
    # 处理所有其他异常
    print(f"An unexpected error occurred: {e}")
    traceback.print_exc()  # 打印详细的异常堆栈
    input("Press Enter to continue...")
    exit()
else:
    # 如果没有异常发生，执行这部分
    print(newDatas)
# finally:
#     # 无论是否发生异常，都会执行这部分
#     # print("Cleaning up...")
#     # print(newDatas)




# # 读取数据
# data1 = sheet.range('A18867').value
# print(data1)

# 获取该工作表的最后一行行号（假设数据区域在A列）
lastRow = sheet.range('A:A').end('down').row
# print(lastRow)
newRow = lastRow + 1
originDatas = sheet.range('A1:A' + str(lastRow)).value
k = 0
for bkno in originDatas:
    k = k + 1
    # if d == bl:
    if bkno[0:2] != 'O_' and any(x[0] == bkno for x in newDatas):
        sheet.range('a' + str(k)).value = 'O_' + bkno


sheet.range(f'a{newRow}:z{newRow + (len(newDatas) - 1)}').value = newDatas



# 下面把源文件夹内的文件全部移动到别处
def move_files(src_dir, dst_dir):
    # 确保目标目录存在
    if not os.path.exists(dst_dir):
        os.makedirs(dst_dir)

    for filename in os.listdir(src_dir):
        src_file = os.path.join(src_dir, filename)
        
        # 检查是否为文件（而非目录）
        if os.path.isfile(src_file):
            dst_file = os.path.join(dst_dir, filename)
            shutil.move(src_file, dst_file)

srcDir = filesFolder
current_time = datetime.now()
formatted_time = current_time.strftime('%Y%m%d%H%M%S')
dstDir = f"F:\\history\\bookings\\{formatted_time}"

move_files(srcDir, dstDir)